﻿/*
Copyright (c) 2009 Lance Ward. Licensed uder the MIT license. See license.txt for the complete description.

Notes on the data class
Clearly there are easier, more secure ways this could have been done. This particular method was chosen for the following reasons
1) It allows me to track and pass around a single class for all interaction with the database.
2) It allows a finer level of control, which is important in a multi-user setting.
3) It allows only retrieving the necessary data, so that the age of the data is limited to when the user starts to view/edit it. This is also important in a multi-user setting.
4) It allows for easy maintenance because all of the queries are stored in one place.
5) It keeps the memory requirements as low as possible
6) It makes migration to a client/server app (R2) simpler by allow a one to one class/function replacement
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Npgsql;
using TreeViewEx;

namespace SpeedTest
{
	public enum ConnectionState
	{
		Connected,
		Disconnected
	}

	public class Data
	{
		private string connectionString = String.Empty;
		//private SQLiteConnection conn = null;
        private NpgsqlConnection conn = null;
		private ConnectionState state = ConnectionState.Disconnected;
		private UserPermissions userPermissions = new UserPermissions();

		public Data()
		{

		}

		public Data(string connectionString)
		{
			if (!String.IsNullOrEmpty(connectionString)) { this.connectionString = connectionString; }
		}

		public UserPermissions User
		{
			get
			{
				return userPermissions;
			}
		}

		public string ConnectionString
		{
			get
			{
				return connectionString;
			}
			set
			{
				connectionString = value;
			}
		}

		public ConnectionState State
		{
			get
			{
				return state;
			}
		}

        public bool Connect(string connectionString)
        {
            this.connectionString = connectionString;
            return Connect();
        }

		public bool Connect()
		{
			if (String.IsNullOrEmpty(connectionString))
			{
				return false;	//TODO: should throw an exception
			}

			try
			{
				conn = new NpgsqlConnection(connectionString);
				conn.Open();
				this.state = ConnectionState.Connected;
			}
			catch(Exception ex)
			{
				//TODO: Error handling
				if (conn != null){conn.Close();}
				return false;
			}

			return true;
		}

		public void Disconnect()
		{
			if (conn != null && conn.State == System.Data.ConnectionState.Open)
			{
				conn.Close();
			}
		}

		public bool Authenticate(string userName, string password)
		{
			DataTable dt = null;
            NamedParameter userParam = new NamedParameter("USERNAME", userName);
            NamedParameter pwdParam = new NamedParameter("PASSWORD", password);
            NamedParameter[] namedParams = new NamedParameter[2] { userParam, pwdParam };

            string sqlStatement = "SELECT Users.ID, ReleasePermissions, EnvironPermissions, PlanPermissions, UserPermissions, RolePermissions, ServerOptPermissions FROM Users JOIN Roles ON Users.RoleID = Roles.ID WHERE Roles.IsWebOnly = false AND UserName = :USERNAME and Password = :PASSWORD";

			dt = GetDataByParams(sqlStatement, namedParams);

			if (dt.Rows.Count > 0)
			{
				userPermissions.ParsePermissions(dt);
				userPermissions.UserID = dt.Rows[0]["ID"].ToString();
			}
			else
			{
				return false;
			}

			return true;
		}

        public bool CheckUserNameAvailability(string userName)
        {
            NamedParameter userParam = new NamedParameter("USERNAME", userName);
            NamedParameter[] namedParams = new NamedParameter[1] { userParam };
            string sqlStatement = "SELECT count(id) FROM Users WHERE username = :USERNAME";
            object obj = GetSingleValueByParams(sqlStatement, namedParams);
            if (obj != DBNull.Value)
            {
                long userCount = (long)obj;

                if (userCount == 0)
                {
                    return true;
                }
                else { return false; }
            }
            else { return false; }
        }

        public bool UpdatePassword(string userId, string oldPassword, string newPassword)
        {
            NamedParameter oldPwdParam = new NamedParameter("OLDPASSWORD", oldPassword);
            NamedParameter newPwdParam = new NamedParameter("NEWPASSWORD", newPassword);
            NamedParameter userIdParam = new NamedParameter("USERID", userId);

            NamedParameter[] namedParams = new NamedParameter[3]{oldPwdParam, newPwdParam, userIdParam};
            string sqlStatement = "SELECT count(1) FROM users WHERE id = :USERID and password = :OLDPASSWORD";

            Object obj = GetSingleValueByParams(sqlStatement,namedParams);
            if (obj == DBNull.Value || (long)obj < 1) { return false; }
            else
            {
                sqlStatement = "UPDATE users SET password = :NEWPASSWORD WHERE id = :USERID";
                return PutDataByParams(sqlStatement, namedParams);
            }
        }

		#region Search

		public DataTable LocateAttachment(string id)
		{
			string sqlStatement = "SELECT Attachments.CaseID, Plans.ID AS PlanID, Plans.ReleaseID  FROM Attachments";
			sqlStatement += " JOIN CaseSuites ON CaseSuites.ID = Attachments.ID";
			sqlStatement += " JOIN Plans ON Plans.ID = CaseSuites.ParentPlanID";
			sqlStatement += " WHERE Attachments.ID = ";
			sqlStatement += SingleQuote(id);
			return GetData(sqlStatement);
		}

		public DataTable LocateCaseSuite(string id)
		{
			string sqlStatement = "SELECT CaseSuites.ID AS CaseID, Plans.ID AS PlanID, Plans.ReleaseID FROM CaseSuites";
			sqlStatement += " JOIN Plans ON Plans.ID = CaseSuites.ParentPlanID";
			sqlStatement += " WHERE CaseSuites.ID = ";
			sqlStatement += SingleQuote(id);
			return GetData(sqlStatement);
		}

		public DataTable LocatePlan(string id)
		{
			string sqlStatement = "SELECT '' as CaseID, Plans.ID AS PlanID, Plans.ReleaseID FROM Plans";
			sqlStatement += " WHERE Plans.ID = ";
			sqlStatement += SingleQuote(id);
			return GetData(sqlStatement);
		}

		public DataTable LocateResult(string id)
		{
			string sqlStatement = "SELECT Results.CaseID, Plans.ID AS PlanID, Plans.ReleaseID  FROM Results";
			sqlStatement += " JOIN Plans ON Plans.ID = Results.PlanID";
			sqlStatement += " WHERE Results.ID = ";
			sqlStatement += SingleQuote(id);
			return GetData(sqlStatement);
		}

		public string PercentQuote(string target)
		{
			return "'%" + target + "%'";
		}

		public DataTable SearchAttachments(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, FileName FROM Attachments WHERE FileName ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

			return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchCaseSteps(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, Steps FROM CaseSuites WHERE NodeType= 2 AND Steps ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();
            
            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchCaseTitles(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, Description FROM CaseSuites WHERE NodeType= 2 AND Description ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchPlanRefNumbers(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, ReferenceNumber FROM Plans WHERE ReferenceNumber ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchPlanTitles(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, Description FROM Plans WHERE Description ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchResultNotes(string searchString, int maxRecords)
        {
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, Notes FROM Results WHERE Notes ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchResultRefNumbers(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, ReferenceNumber FROM Results WHERE ReferenceNumber ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchSuiteSetups(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, Steps FROM CaseSuites WHERE NodeType= 1 AND Steps ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		public DataTable SearchSuiteTitles(string searchString, int maxRecords)
		{
            NamedParameter searchParam = new NamedParameter("SEARCHSTRING", searchString);
            NamedParameter[] namedParams = new NamedParameter[1] { searchParam };

            string sqlStatement = "SELECT ID, Description FROM CaseSuites WHERE NodeType= 1 AND Description ILIKE '%' || :SEARCHSTRING || '%'";
			sqlStatement += " LIMIT " + maxRecords.ToString();

            return GetDataByParams(sqlStatement, namedParams);
		}

		#endregion

		#region Stats
		public long GetTotalCases(string planID, string envID)
		{

			string sqlStmt = "SELECT Count(CaseSuites.ID) FROM CaseSuites JOIN CaseEnvirons ON CaseSuites.ID = CaseEnvirons.CaseID WHERE CaseSuites.NodeType = 2 AND CaseSuites.ParentPlanID = " + SingleQuote(planID);
			sqlStmt += " AND CaseEnvirons.EnvironmentID = " + SingleQuote(envID);
			DataTable table = GetData(sqlStmt);
			if (table != null && table.Rows.Count > 0)
			{
				return (long)table.Rows[0][0];
			}
			else { return 0; }	//default value
		}

		public long GetTotalRunCases(string planID, string envID)
		{
			string sqlStmt = "SELECT Count(Results.ID) FROM Results WHERE Results.PlanID = " + SingleQuote(planID);
			sqlStmt += " AND Results.EnvironmentID = " + SingleQuote(envID);
			DataTable table = GetData(sqlStmt);
			if (table != null && table.Rows.Count > 0)
			{
				return (long)table.Rows[0][0];
			}
			else { return 0; }	//default value
		}

		public DataTable GetRunResultsSummary(string planID, string envID)
		{
			string sqlStatement = "SELECT resulttext.result AS result, coalesce(resultsummary.total, 0) AS total FROM resulttext";
			sqlStatement += " LEFT JOIN (";
			sqlStatement += " 	SELECT result, count(id) as total FROM casesuites";
			sqlStatement += " 	LEFT JOIN (";
			sqlStatement += " 		select caseenvirons.caseid, caseenvirons.environmentid, coalesce(results.result, 'NOT RUN') as result from caseenvirons";
			sqlStatement += " 		left join (";
			sqlStatement += " 			select * from results where results.active = 'TRUE'";
			sqlStatement += " 		) as results on caseenvirons.caseid = results.caseid and caseenvirons.environmentid = results.environmentid";
			sqlStatement += " 		join environments on caseenvirons.environmentid = environments.id";
			sqlStatement += " 	) AS allresults ON allresults.caseid = casesuites.id";
			sqlStatement += " 	WHERE allresults.environmentid = :ENVIRONMENTID AND parentplanid = :PLANID AND casesuites.nodetype = 2";
			sqlStatement += " 	GROUP BY allresults.result";
			sqlStatement += " ) AS resultsummary on resultsummary.result = resulttext.result";
			sqlStatement += " ORDER BY resulttext.result ASC";

			NamedParameter planParam = new NamedParameter("PLANID", planID);
			NamedParameter envParam = new NamedParameter("ENVIRONMENTID", envID);
			NamedParameter[] namedParams = new NamedParameter[2]{planParam, envParam};

			return GetDataByParams(sqlStatement, namedParams);

		}
		
		#endregion

		#region Environ Assignments

		public DataTable GetEnvironAssignmentsByCaseID(string id)
		{
			string sqlStatement = "SELECT ID, CaseID, EnvironmentID FROM CaseEnvirons WHERE CaseID = " + SingleQuote(id) + "ORDER BY ID ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetEnvironAssignmentByID(string id)
		{
			string sqlStatement = "SELECT ID, Description FROM Environments WHERE ID = " + SingleQuote(id);

			return GetData(sqlStatement);
		}

		public long InsertEnvironAssignment(DataTable table)
		{
            NamedParameter caseParam = new NamedParameter("CASEID", table.Rows[0]["CaseID"].ToString());
            NamedParameter envParam = new NamedParameter("ENVIRONMENTID", table.Rows[0]["EnvironmentID"].ToString());
            NamedParameter[] namedParams = new NamedParameter[2] { caseParam, envParam };

			string sqlStatement = "INSERT INTO CaseEnvirons (CaseID, EnvironmentID) VALUES (:CASEID, :ENVIRONMENTID) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteEnvironAssignmentByCaseID(string id)
		{
			string sqlStatement = "DELETE FROM CaseEnvirons WHERE CaseID = " + SingleQuote(id);
			return PutData(sqlStatement);
		}

		#endregion

		#region Tester Assignments

		public DataTable GetTesterAssignmentsByCaseID(string id)
		{
			string sqlStatement = "SELECT CaseTesters.ID, CaseTesters.CaseID, CaseTesters.TesterID, Users.Name FROM CaseTesters";
			sqlStatement += " JOIN Users ON CaseTesters.TesterID = Users.ID";
			sqlStatement += " WHERE CaseTesters.CaseID =  ";
			sqlStatement += SingleQuote(id) + " ORDER BY CaseTesters.ID ASC";
			
			return GetData(sqlStatement);
		}

		public DataTable GetTesterAssignmentByID(string id)
		{
			string sqlStatement = "SELECT ID, Description FROM Environments WHERE ID = " + SingleQuote(id);

			return GetData(sqlStatement);
		}

		public long InsertTesterAssignment(DataTable table)
		{
            NamedParameter caseParam = new NamedParameter("CASEID", table.Rows[0]["CaseID"].ToString());
            NamedParameter testerParam = new NamedParameter("TESTERID", table.Rows[0]["TesterID"].ToString());
            NamedParameter[] namedParams = new NamedParameter[2] { caseParam, testerParam };

			string sqlStatement = "INSERT INTO CaseTesters (CaseID, TesterID) VALUES (:CASEID, :TESTERID) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteTesterAssignmentByCaseID(string id)
		{
			string sqlStatement = "DELETE FROM CaseTesters WHERE CaseID = " + SingleQuote(id);
			return PutData(sqlStatement);
		}


		#endregion

		#region results

		public DataTable GetResultsByPlanID(string planID, string envID)
		{
			string sqlStatement = "SELECT Results.CaseID, Results.Result FROM Results WHERE PlanID = " + planID + " AND EnvironmentID = " + envID + " AND Active = 'TRUE'";

			return GetData(sqlStatement);
		}

		public DataTable GetResultsByCaseID(string caseID, string envID)
		{
			string sqlStatement = "SELECT Results.ID, Results.PlanID, Results.CaseID, Results.EnvironmentID,";
			sqlStatement += " Results.Result, Results.ReferenceNumber, Results.Notes, Results.ExecutionDate, Users.Name,";
			sqlStatement += " Results.Active FROM Results LEFT OUTER JOIN Users ON Results.UserID = Users.ID";
			sqlStatement += " WHERE Results.CaseID = " + SingleQuote(caseID) + " AND Results.EnvironmentID = " + SingleQuote(envID); 
			sqlStatement += " ORDER BY Results.ID DESC";

			return GetData(sqlStatement);
		}

		public DataTable GetResultByID(string id)
		{
			string sqlStatement = "SELECT ID, Description FROM Environments WHERE ID = " + SingleQuote(id);

			return GetData(sqlStatement);
		}

		public bool DeactivateResultByID(string id)
		{
			string sqlStatement = "UPDATE Results SET Active = 'FALSE' WHERE ID = " + SingleQuote(id);
			return PutData(sqlStatement);
		}

		public DataTable GetActiveResultByCaseID(string id, string envId)
		{
			string sqlStatement = "SELECT ID, Result FROM Results WHERE Active = 'TRUE' AND CaseID = " + SingleQuote(id) + " AND EnvironmentID = " + SingleQuote(envId);
			return GetData(sqlStatement);
		}

		public long InsertResult(DataTable table)
		{
			NamedParameter planParam = new NamedParameter("PLANID", table.Rows[0]["PlanID"].ToString());
			NamedParameter caseParam = new NamedParameter("CASEID", table.Rows[0]["CaseID"].ToString());
			NamedParameter envParam = new NamedParameter("ENVIRONID", table.Rows[0]["EnvironmentID"].ToString());
			NamedParameter resParam = new NamedParameter("RESULT", table.Rows[0]["Result"].ToString());
			NamedParameter refParam = new NamedParameter("REFNUMBER", table.Rows[0]["ReferenceNumber"].ToString());
			NamedParameter noteParam = new NamedParameter("NOTES", table.Rows[0]["Notes"].ToString());
			NamedParameter userParam = new NamedParameter("USERID", table.Rows[0]["UserID"].ToString());
			NamedParameter activeParam = new NamedParameter("ACTIVE", table.Rows[0]["Active"].ToString());
			NamedParameter[] namedParams = new NamedParameter[8]{planParam, caseParam, envParam, resParam, refParam, 
				noteParam, userParam, activeParam};
			
			string sqlStatement = "INSERT INTO Results (ExecutionDate, PlanID, CaseID, EnvironmentID, Result, ReferenceNumber, Notes, UserID, Active) ";
			sqlStatement += " VALUES (Date('now'), :PLANID, :CASEID, :ENVIRONID, :RESULT, :REFNUMBER, :NOTES, :USERID, :ACTIVE) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteResultByID(string id)
		{
			string sqlStatement = "DELETE FROM Environments WHERE ID = " + SingleQuote(id);
			return PutData(sqlStatement);
		}

		#endregion

		#region attachments

		public long GetAttachmentIDByName(string caseID, string fileName)
		{
			string sqlStatement = "SELECT ID FROM Attachments WHERE CaseID = " + SingleQuote(caseID) + " AND FileName = " + SingleQuote(fileName);
			object obj = GetSingleValue(sqlStatement);
			if (obj != DBNull.Value)
			{
				return (long)obj;
			}
			else
			{
				return -1;
			}
		}

        public string GetAttachmentNameByID(string id)
        {
            string sqlStatement = "SELECT FileName FROM Attachments WHERE ID = " + SingleQuote(id);
            object obj = GetSingleValue(sqlStatement);
            if (obj != DBNull.Value)
            {
                return (string)obj;
            }
            else
            {
                return String.Empty;
            }
        }

		public DataTable GetAttachmentsByCaseID(string caseID)
		{
			string sqlStatement = "SELECT ID, FileName FROM Attachments WHERE CaseID = " + SingleQuote(caseID) + " ORDER BY FileName ASC";

			return GetData(sqlStatement);
		}

		public long InsertAttachment(DataTable table)
		{
			NamedParameter caseParam = new NamedParameter("CASEID", table.Rows[0]["CaseID"].ToString());
			NamedParameter fileParam = new NamedParameter("FILENAME", table.Rows[0]["FileName"].ToString());
			NamedParameter userParam = new NamedParameter("UPLOADEDBY", table.Rows[0]["UploadedBy"].ToString());
			NamedParameter[] namedParams = new NamedParameter[3]{caseParam, fileParam, userParam};

			string sqlStatement = "INSERT INTO Attachments (CaseID, FileName, UploadedBy) ";
			sqlStatement += "VALUES ( :CASEID, :FILENAME, :UPLOADEDBY ) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteAttachmentByID(string attachID)
		{
			string sqlStatement = "DELETE FROM Attachments WHERE ID = " + SingleQuote(attachID);
			return PutData(sqlStatement);
		}

		public bool UpdateAttachmentByID(DataTable attachTable, string attachID)
		{
			string sqlStatement = "UPDATE Attachments SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, attachTable);

			sqlStatement += " WHERE ID = " + SingleQuote(attachID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		#endregion

		#region environments

		public DataTable GetEnvironments()
		{
			string sqlStatement = "SELECT ID, Description FROM Environments ORDER BY ID ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetEnvironmentByID(string envID)
		{
			string sqlStatement = "SELECT ID, Description FROM Environments WHERE ID = " + SingleQuote(envID);

			return GetData(sqlStatement);
		}

		public bool UpdateEnvironmentByID(DataTable envTable, string envID)
		{
			string sqlStatement = "UPDATE Environments SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, envTable);

			sqlStatement += " WHERE ID = " + SingleQuote(envID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		public long InsertEnvironment(DataTable envTable)
		{
			NamedParameter envParam = new NamedParameter("ENVDESCRIPTION", envTable.Rows[0]["Description"].ToString());
			NamedParameter[] namedParams = new NamedParameter[1] { envParam };

			string sqlStatement = "INSERT INTO Environments (Description) ";
			sqlStatement += " VALUES ( :ENVDESCRIPTION ) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteEnvironmentByID(string envID)
		{
			NamedParameter envParam = new NamedParameter("ENVIRONMENTID", envID);
			NamedParameter[] namedParams = new NamedParameter[1] { envParam };

			string sqlStatement = "DELETE FROM results WHERE results.environid = :ENVIRONMENTID;";
			sqlStatement += " DELETE FROM caseenvirons WHERE caseenvirons.environmentid = :ENVIRONMENTID;";
			sqlStatement += " DELETE FROM environments WHERE environments.id = :ENVIRONMENTID;";

			return PutDataByParams(sqlStatement, namedParams);
		}

		#endregion

		#region releases

		public DataTable GetActiveReleases()
		{
			string sqlStatement = "SELECT ID, Description FROM Releases WHERE Active = 'TRUE' ORDER BY ExpirationDate ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetReleases()
		{
			string sqlStatement = "SELECT ID, Description FROM Releases ORDER BY ExpirationDate ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetReleaseByID(string relID)
		{
			string sqlStatement = "SELECT Description, ExpirationDate, Active FROM Releases WHERE ID = " + SingleQuote(relID);

			return GetData(sqlStatement);
		}

		public bool UpdateReleaseByID(DataTable relTable, string relID)
		{
			string sqlStatement = "UPDATE Releases SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, relTable);

			sqlStatement += " WHERE ID = " + SingleQuote(relID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		public long InsertRelease(DataTable relTable)
		{
			NamedParameter descParam = new NamedParameter("DESCRIPTION", relTable.Rows[0]["Description"].ToString());
			NamedParameter expParam = new NamedParameter("EXPIRATIONDATE", relTable.Rows[0]["ExpirationDate"].ToString());
			NamedParameter activeParam = new NamedParameter("ACTIVE", relTable.Rows[0]["Active"].ToString());
			NamedParameter[] namedParams = new NamedParameter[3] { descParam, expParam, activeParam };

			string sqlStatement = "INSERT INTO Releases (Description, ExpirationDate, Active) ";
			sqlStatement += " VALUES ( :DESCRIPTION, :EXPIRATIONDATE, :ACTIVE ) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteReleaseByID(string relID)
		{
			//string sqlStatement = "DELETE FROM Releases WHERE ID = " + SingleQuote(relID);
			//return PutData(sqlStatement);

            NamedParameter relParam = new NamedParameter();
            relParam.Name = "RELEASEID";
            relParam.Value = relID;

            NamedParameter[] namedParams = new NamedParameter[1]{relParam};

            string sqlStatement = " DELETE FROM attachments USING casesuites, plans, releases";
            sqlStatement += " WHERE attachments.caseid = casesuites.id AND casesuites.parentplanid = plans.id AND plans.releaseid = releases.id AND releases.id = :RELEASEID;";

            sqlStatement += " DELETE FROM casetesters USING casesuites, plans, releases";
            sqlStatement += " WHERE casetesters.caseid = casesuites.id AND casesuites.parentplanid = plans.id AND plans.releaseid = releases.id AND releases.id = :RELEASEID;";

            sqlStatement += " DELETE FROM caseenvirons USING casesuites, plans, releases";
            sqlStatement += " WHERE caseenvirons.caseid = casesuites.id AND casesuites.parentplanid = plans.id AND plans.releaseid = releases.id AND releases.id = :RELEASEID;";

            sqlStatement += " DELETE FROM casesuites USING plans, releases";
            sqlStatement += " WHERE casesuites.parentplanid = plans.id AND plans.releaseid = releases.id AND releases.id = :RELEASEID;";

            sqlStatement += " DELETE FROM plans USING releases";
            sqlStatement += " WHERE plans.releaseid = releases.id AND releases.id = :RELEASEID;";

            sqlStatement += " DELETE FROM releases WHERE releases.id = :RELEASEID;";
            sqlStatement += " DELETE FROM results USING plans, releases";
            sqlStatement += " WHERE results.planid = plans.id AND plans.releaseid = releases.id AND releases.id = :RELEASEID;";

            return PutDataByParams(sqlStatement, namedParams);
		}

		#endregion

		#region roles

		public DataTable GetRoles()
		{
			string sqlStatement = "SELECT ID, Description FROM Roles ORDER BY Description ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetRoleByID(string roleID)
		{
			string sqlStatement = "SELECT Description, EnvironPermissions, PlanPermissions, ReleasePermissions, RolePermissions, ServerOptPermissions, UserPermissions, IsWebOnly FROM Roles WHERE ID = " + SingleQuote(roleID);

			return GetData(sqlStatement);
		}

		public long GetRoleUsageCount(string id)
		{
			string sqlStatement = "SELECT count(1) FROM users JOIN roles on users.roleid = roles.id AND users.roleid = :ROLEID";
			NamedParameter roleParam = new NamedParameter("ROLEID", id);
			NamedParameter[] namedParams = new NamedParameter[1] { roleParam };

			object obj = GetSingleValueByParams(sqlStatement, namedParams);
			if (obj != DBNull.Value)
			{
				long roleCount = (long)obj;
				return roleCount;
			}
			
			return 0;
		}

		public bool UpdateRoleByID(DataTable roleTable, string roleID)
		{
			string sqlStatement = "UPDATE Roles SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, roleTable);

			sqlStatement += " WHERE ID = " + SingleQuote(roleID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		public long InsertRole(DataTable roleTable)
		{
			NamedParameter descParam = new NamedParameter("DESCRIPTION", roleTable.Rows[0]["Description"].ToString());
			NamedParameter envParam = new NamedParameter("ENVIRONPERM", roleTable.Rows[0]["EnvironPermissions"].ToString());
			NamedParameter planParam = new NamedParameter("PLANPERM", roleTable.Rows[0]["PlanPermissions"].ToString());
			NamedParameter relParam = new NamedParameter("RELPERM", roleTable.Rows[0]["ReleasePermissions"].ToString());
			NamedParameter roleParam = new NamedParameter("ROLEPERM", roleTable.Rows[0]["RolePermissions"].ToString());
			NamedParameter servParam = new NamedParameter("SERVOPTPERM", roleTable.Rows[0]["ServerOptPermissions"].ToString());
			NamedParameter userParam = new NamedParameter("USERPERM", roleTable.Rows[0]["UserPermissions"].ToString());
			NamedParameter[] namedParams = new NamedParameter[7] { descParam, envParam, planParam, relParam,
				roleParam, servParam, userParam };

			string sqlStatement = "INSERT INTO Roles (Description, EnvironPermissions, PlanPermissions, ReleasePermissions, RolePermissions, ServerOptPermissions, UserPermissions) ";
			sqlStatement += "VALUES ( :DESCRIPTION, :ENVIRONPERM, :PLANPERM, :RELPERM, :ROLEPERM, :SERVOPTPERM, :USERPERM) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteRoleByID(string roleID)
		{
			string sqlStatement = "DELETE FROM roles WHERE roles.id = :ROLEID;";
			NamedParameter roleParam = new NamedParameter("ROLEID", roleID);
			NamedParameter[] namedParams = new NamedParameter[1] { roleParam };

			return PutDataByParams(sqlStatement, namedParams);
		}

		#endregion

		#region users

		public DataTable GetUsers()
		{
			string sqlStatement = "SELECT ID, UserName FROM Users ORDER BY UserName ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetUserByID(string userID)
		{
            string sqlStatement = "SELECT ID, Name, UserName, Password, Email, RoleID, Active FROM Users WHERE Users.ID = " + SingleQuote(userID);

			return GetData(sqlStatement);
		}

		public bool UpdateUserByID(DataTable userTable, string userID)
		{
            string sqlStatement = "UPDATE Users SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, userTable);

            sqlStatement += " WHERE ID = " + SingleQuote(userID);

            return PutDataByParams(sqlStatement, namedParams);
		}

		public long InsertUser(DataTable userTable)
		{
			NamedParameter[] namedParams = new NamedParameter[6];
            string sqlStatement = "INSERT INTO Users (Name, UserName, Password, Email, RoleID, Active) ";
			sqlStatement += " VALUES ( :NAME, :USERNAME, :PASSWORD, :EMAIL, :ROLEID, :ACTIVE ) RETURNING id; ";

			namedParams[0] = new NamedParameter("NAME", userTable.Rows[0]["Name"].ToString());
			namedParams[1] = new NamedParameter("USERNAME", userTable.Rows[0]["UserName"].ToString());
			namedParams[2] = new NamedParameter("PASSWORD", userTable.Rows[0]["Password"].ToString());
			namedParams[3] = new NamedParameter("EMAIL", userTable.Rows[0]["Email"].ToString());
			namedParams[4] = new NamedParameter("ROLEID", userTable.Rows[0]["RoleID"].ToString());
			namedParams[5] = new NamedParameter("ACTIVE", userTable.Rows[0]["Active"].ToString());

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteUserByID(string userID)
		{
			string sqlStatement = " DELETE FROM users WHERE users.id = :USERID;";
			sqlStatement += " DELETE FROM casetesters WHERE casetesters.testerid = :USERID;";

			NamedParameter userParam = new NamedParameter("USERID", userID);
			NamedParameter[] namedParams = new NamedParameter[1] { userParam };

			return PutDataByParams(sqlStatement, namedParams);
		}

		#endregion

		#region server settings

		public DataTable GetServerSettings()
		{
			string sqlStatement = "SELECT ID, Path, AutoActivateUsers, DefaultRole FROM ServerOptions";

			return GetData(sqlStatement);
		}

		public bool UpdateServerSettings(DataTable serverTable, string rowID)
		{
			string sqlStatement = "UPDATE ServerOptions SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, serverTable);

			sqlStatement += " WHERE ID = " + SingleQuote(rowID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		#endregion

		public DataTable GetPlansByRelease(string releaseID)
		{
			string sqlStatement = "SELECT ID, ReferenceNumber, Description FROM Plans WHERE ReleaseID = '" + releaseID + "' ORDER BY ID ASC";

			return GetData(sqlStatement);
		}

		//used for filtered and unfiltered results
		public DataTable GetCaseSuitesByFilter(Filter filter)
		{
			//TODO: build select based on filter 
			string sqlStatement = "SELECT CaseSuites.ID, CaseSuites.ParentPlanID, CaseSuites.NodeType, CaseSuites.Path, CaseSuites.Description FROM CaseSuites WHERE CaseSuites.ParentPlanID = '" + filter.Plan + "' ORDER BY CaseSuites.Path ASC";

			return GetData(sqlStatement);
		}

		//used for filter and unfiltered - for setting the bg color on the tree
		public DataTable GetCaseSuiteResultsByFilter(Filter filter)
		{
			string sqlStatement = "SELECT * FROM ("; ;
			sqlStatement += "SELECT "; ;
			sqlStatement += " CaseSuites.ID AS ID, CaseSuites.ParentPlanID AS ParentPlanID, CaseSuites.NodeType AS NodeType, CaseSuites.Path AS Path, ";
			sqlStatement += " CaseSuites.Description AS Description, Results.Result AS Result ";
			sqlStatement += " FROM CaseSuites";
			sqlStatement += " LEFT OUTER JOIN";
			sqlStatement += " (";
			sqlStatement += "       SELECT * FROM CaseEnvirons WHERE EnvironmentID = " + SingleQuote(filter.Environment) + " ";
			sqlStatement += " ) AS CaseEnvirons ON CaseSuites.ID = CaseEnvirons.CaseID ";

			sqlStatement += " LEFT OUTER JOIN";
			sqlStatement += " (";
			sqlStatement += "       SELECT * FROM Results WHERE Active = 'TRUE' ";
			sqlStatement += " ) AS Results ON CaseSuites.ID = Results.CaseID ";

			sqlStatement += " WHERE CaseSuites.ParentPlanID = " + SingleQuote(filter.Plan) + " AND NodeType = 1 ";

			sqlStatement += " UNION ";

			sqlStatement += " SELECT * FROM ";
			sqlStatement += " ( ";
			sqlStatement += "      SELECT ";
			sqlStatement += "      CaseSuites.ID AS ID, CaseSuites.ParentPlanID AS ParentPlanID, CaseSuites.NodeType AS NodeType, CaseSuites.Path AS Path, ";
			sqlStatement += "      CaseSuites.Description AS Description, Results.Result AS Result ";
			sqlStatement += "      FROM CaseSuites ";
			sqlStatement += "      LEFT OUTER JOIN ";
			sqlStatement += "      ( ";
			sqlStatement += "           SELECT * FROM CaseEnvirons WHERE EnvironmentID = " + SingleQuote(filter.Environment) + " ";
			sqlStatement += "      ) AS CaseEnvirons ON CaseSuites.ID = CaseEnvirons.CaseID ";

			sqlStatement += "      LEFT OUTER JOIN ";
			sqlStatement += "      ( ";
			sqlStatement += "           SELECT * FROM Results WHERE Active = 'TRUE' and EnvironmentID = " + SingleQuote(filter.Environment) + " ";
			sqlStatement += "      ) AS Results ON CaseSuites.ID = Results.CaseID ";

			if (!String.IsNullOrEmpty(filter.AssignedTester))
			{
				sqlStatement += "      JOIN ";
				sqlStatement += "      ( ";
				sqlStatement += "           SELECT * FROM CaseTesters WHERE TesterID = " + SingleQuote(filter.AssignedTester) + " ";
				sqlStatement += "      ) AS CaseTesters ON CaseSuites.ID = CaseTesters.CaseID ";
			}

			sqlStatement += "      WHERE ";
			sqlStatement += "      (CaseSuites.ParentPlanID = " + SingleQuote(filter.Plan) + " AND NodeType = 2) ";
			sqlStatement += " ) AS CaseSuites ";
			




			if (filter.IncludeNotRun || !String.IsNullOrEmpty(filter.AllResults))
			{
				sqlStatement += " WHERE ";

				if (filter.IncludeNotRun)
				{
					sqlStatement += " Result is null ";
				}

				if (filter.IncludeNotRun && !String.IsNullOrEmpty(filter.AllResults)) { sqlStatement += " OR "; }

				if (!String.IsNullOrEmpty(filter.AllResults))
				{
					sqlStatement += "  Result in ( " + filter.AllResults + ") ";
				}
			}

            sqlStatement += " ) AS CaseSuiteResults ORDER BY Path ASC ";

			return GetData(sqlStatement);
		}

		public DataTable GetCaseSuitesByRelease(string releaseID)
		{
			string sqlStatement = "SELECT CaseSuites.ID, CaseSuites.ParentPlanID, CaseSuites.NodeType, CaseSuites.Path, CaseSuites.Description FROM CaseSuites JOIN Plans ON CaseSuites.ParentPlanID = Plans.ID JOIN Releases ON Plans.ReleaseID = Releases.ID WHERE Releases.ID = '" + releaseID + "' ORDER BY CaseSuites.ParentPlanID ASC, CaseSuites.Path ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetUserNames()
		{
			string sqlStatement = "SELECT Users.ID, Users.Name FROM Users ORDER BY Users.Name ASC";

			return GetData(sqlStatement);
		}

		public DataTable GetCaseSuiteByID(string caseSuiteID, NodeType caseSuiteType)
		{
			string sqlStatement = "SELECT Description, LastModifiedByID, DateModified, Category, Priority, Steps FROM CaseSuites WHERE CaseSuites.NodeType = '" + (int)caseSuiteType + "' AND CaseSuites.ID = '" + caseSuiteID + "'";

			return GetData(sqlStatement);
		}

		public bool UpdateCaseSuiteByID(DataTable caseTable, string caseID)
		{
			string sqlStatement = "UPDATE CaseSuites SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, caseTable);

			sqlStatement += " WHERE ID = " + SingleQuote(caseID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		public string Bracket(string input)
		{
			string output = "" + input + "";
			return output;
		}

		public string SingleQuote(string input)
		{
			string output = "'" + input + "'";
			return output;
		}

		//TODO: consolidate MOVE, COPY and DUPLICATE functions
        public bool MoveCaseSuiteInPlan(string id, string targetId, DropPosition position)
        {
            //This function is essentially a delete and insert, performed in succession.

			NodePath sourcePath = new NodePath();
			NodePath targetPath = new NodePath();

			NamedParameter sourceParam = new NamedParameter("SOURCEID", id);
            NamedParameter targetParam = new NamedParameter("TARGETID", targetId);
			NamedParameter sourcePathParam = new NamedParameter("SOURCEPATH", String.Empty);
			NamedParameter sourceParentPathParam = new NamedParameter("SOURCEPARENTPATH", String.Empty);
			NamedParameter targetPathParam = new NamedParameter("TARGETPATH", String.Empty);
			NamedParameter targetParentPathParam = new NamedParameter("TARGETPARENTPATH", String.Empty);
			NamedParameter planParam = new NamedParameter("PLANID", String.Empty);

			NamedParameter[] namedParams = new NamedParameter[7] { sourceParam, sourcePathParam, targetParam, targetPathParam, planParam, sourceParentPathParam, targetParentPathParam};
			string sqlStatement = "SELECT id, path, parentplanid FROM casesuites WHERE id = :SOURCEID";
			DataTable sourceNodeTable = GetDataByParams(sqlStatement, namedParams);
			

			if(sourceNodeTable == null || sourceNodeTable.Rows.Count < 1) { return false; }
			//TODO: make sure the target is not a child of the source.

			planParam.Value = sourceNodeTable.Rows[0]["parentplanid"].ToString();
			sourcePath.Path = sourcePathParam.Value = sourceNodeTable.Rows[0]["path"].ToString();
			
			//REAL STUFF STARTS HERE
			//Source nodes and children
			sqlStatement = "SELECT id, path FROM casesuites WHERE path LIKE :SOURCEPATH || '%' AND parentplanid = :PLANID";
			DataTable sourceTable = GetDataByParams(sqlStatement, namedParams);
            //Update later in function

			//Sub-source nodes and children
            //Essentially a delete of the source nodes.
			DataTable subSourceTable = null;
            sourceParentPathParam.Value = sourcePath.Parent;
			sqlStatement = "SELECT id, path FROM casesuites WHERE path > :SOURCEPATH AND path LIKE (:SOURCEPARENTPATH || '%') AND parentplanid = :PLANID; ";
			subSourceTable = GetDataByParams(sqlStatement, namedParams);

            PathUtil.DecrementPaths(ref subSourceTable, sourcePath.Level);
            UpdatePathsByID(subSourceTable);

            //Target nodes and children
            sqlStatement = "SELECT id, path FROM casesuites WHERE id = :TARGETID";
            DataTable targetNodeTable = GetDataByParams(sqlStatement, namedParams);
            targetPath.Path = targetPathParam.Value = targetNodeTable.Rows[0]["path"].ToString();

			switch (position)
			{
				case DropPosition.DropIn:
					targetPathParam.Value = targetPath.Path = targetPath.FirstChild;
					break;
				case DropPosition.DropBelow:
					targetPathParam.Value = targetPath.Path = targetPath.Increment();
					break;
				case DropPosition.DropAbove:	//left for clarity of code, but do nothing.
					break;
			}
			targetParentPathParam.Value = targetPath.Parent;
			sqlStatement = "SELECT id, path FROM casesuites WHERE path >= :TARGETPATH AND path LIKE (:TARGETPARENTPATH || '%')";
            sqlStatement += " AND id NOT IN (SELECT id FROM casesuites WHERE path LIKE :SOURCEPATH || '%' AND parentplanid = :PLANID)";
            sqlStatement += " AND parentplanid = :PLANID";
			DataTable subTargetTable = GetDataByParams(sqlStatement, namedParams);

            PathUtil.IncrementPaths(ref subTargetTable, targetPath.Level);
            UpdatePathsByID(subTargetTable);

            //Update Source Nodes
			PathUtil.ChangePathParents(ref sourceTable, targetPath.Path, sourcePath.Path);
			UpdatePathsByID(sourceTable);
			
			return true;
        }

        public bool MoveCaseSuiteByID(string id, string targetRelease, string targetPlan)
        {
            bool result = CopyCaseSuiteByID(id, targetRelease, targetPlan, true, true, true);

            if (result)
            {
                result = DeleteCaseSuiteByID(id);
            }

            return result;
        }

		public bool DuplicateCaseSuiteByID(string id)
		{
			//get the node and all child nodes
			NamedParameter planParam = new NamedParameter("PLANID", String.Empty);
			NamedParameter caseParam = new NamedParameter("CASEID", id);
			NamedParameter newCaseParam = new NamedParameter("NEWCASEID", String.Empty);
			NamedParameter pathParam = new NamedParameter("PATH", String.Empty);

			NamedParameter[] namedParams = new NamedParameter[4] { planParam, caseParam, newCaseParam, pathParam };

			string sqlStatement = "SELECT path, parentplanid FROM casesuites WHERE id = :CASEID";
			DataTable caseTable = GetDataByParams(sqlStatement, namedParams);
			if (caseTable == null || caseTable.Rows.Count < 1) { return false; }

			planParam.Value = caseTable.Rows[0]["ParentPlanId"].ToString();
			pathParam.Value = caseTable.Rows[0]["Path"].ToString();

			//get the casetesters, caseenvirons and attachments for all nodes
			sqlStatement = "SELECT id, path FROM casesuites WHERE path LIKE :PATH || '%' AND parentplanid = :PLANID ";
			DataTable caseIdsTable = GetDataByParams(sqlStatement, namedParams);

			NodePath path = new NodePath(pathParam.Value);
			string parentPath = GetCaseSuitePath(planParam.Value, path.Parent);
			PathUtil.ChangePathParents(ref caseIdsTable, parentPath, pathParam.Value);

			//create a copy and insert at the bottom of the parent node
			if (caseIdsTable != null)
			{
				for (int i = 0; i < caseIdsTable.Rows.Count; i++)
				{
					pathParam.Value = caseIdsTable.Rows[i]["path"].ToString();
					caseParam.Value = caseIdsTable.Rows[i][0].ToString();

					sqlStatement = "INSERT INTO caseSuites (parentplanid, nodetype, path, description, creatorid, lastmodifiedbyid, datecreated, datemodified, category, priority, steps)";
					sqlStatement += " ( SELECT parentplanid, nodetype, :PATH as path, description, creatorid, lastmodifiedbyid, ";
					sqlStatement += " datecreated, datemodified, category, priority, steps FROM casesuites WHERE id = :CASEID ) RETURNING id;";
					long newCaseID = InsertDataByParams(sqlStatement, namedParams);

					newCaseParam.Value = newCaseID.ToString();

					sqlStatement = String.Empty;

					sqlStatement += " INSERT INTO casetesters (caseid, testerid)";
					sqlStatement += " ( SELECT :NEWCASEID as caseid, casetesters.testerid FROM casetesters WHERE casetesters.caseid = :CASEID );";

					sqlStatement += " INSERT INTO caseenvirons (caseid, environmentid)";
					sqlStatement += " ( SELECT :NEWCASEID as caseid, caseenvirons.environmentid FROM caseenvirons WHERE caseenvirons.caseid = :CASEID );";

					InsertDataByParams(sqlStatement, namedParams);
				}
			}

			return true;
		}

        public bool CopyCaseSuiteByID(string id, string targetRelease, string targetPlan, bool copyResults, bool copyTesters, bool copyEnvirons)
        {
            NamedParameter parentCaseParam = new NamedParameter("PARENTCASEID", id);
            NamedParameter relParam = new NamedParameter("RELEASEID", targetRelease);
            NamedParameter newPlanParam = new NamedParameter("NEWPLANID", targetPlan);
            NamedParameter oldCaseParam = new NamedParameter("OLDCASEID", String.Empty);
            NamedParameter newCaseParam = new NamedParameter("NEWCASEID", String.Empty);
            NamedParameter pathParam = new NamedParameter("PATH", String.Empty);
            NamedParameter oldPlanParam = new NamedParameter("OLDPLANID", String.Empty);

            NamedParameter[] namedParams = new NamedParameter[7] { parentCaseParam, relParam, 
                newPlanParam, oldCaseParam, newCaseParam, pathParam, oldPlanParam };

            string sqlStatement = "SELECT id, path, parentplanid FROM casesuites WHERE id = :PARENTCASEID";
            DataTable caseTable = GetDataByParams(sqlStatement, namedParams);
            if (caseTable == null || caseTable.Rows.Count < 1) { return false; }

            oldPlanParam.Value = caseTable.Rows[0]["parentplanid"].ToString();
            string casePath = caseTable.Rows[0]["path"].ToString();
            pathParam.Value = casePath + "%";

            sqlStatement = "SELECT id, path FROM casesuites WHERE path LIKE :PATH AND parentplanid = :OLDPLANID ";
            DataTable caseIdsTable = GetDataByParams(sqlStatement, namedParams);

            //if destination is null - assume it's the end, otherwise get the data for the destination
            //the two lines below handle the source node path changes
            string parentPath = GetCaseSuitePath(newPlanParam.Value, String.Empty);
            PathUtil.ChangePathParents(ref caseIdsTable, parentPath, pathParam.Value);

            //TODO: code to handle the destination node path changes


            //copy these for each id 
            if (caseIdsTable != null)
            {
                for (int i = 0; i < caseIdsTable.Rows.Count; i++)
                {
                    //Get the new parent path
                    pathParam.Value = caseIdsTable.Rows[i]["path"].ToString();
                    //insert new case
                    oldCaseParam.Value = caseIdsTable.Rows[i][0].ToString();
                    sqlStatement = "INSERT INTO caseSuites (parentplanid, nodetype, path, description, creatorid, lastmodifiedbyid, datecreated, datemodified, category, priority, steps)";
                    sqlStatement += " ( SELECT :NEWPLANID as parentplanid, nodetype, :PATH as path, description, creatorid, lastmodifiedbyid, ";
                    sqlStatement += " datecreated, datemodified, category, priority, steps FROM casesuites WHERE id = :OLDCASEID ) RETURNING id;";
                    long newCaseID = InsertDataByParams(sqlStatement, namedParams);

                    newCaseParam.Value = newCaseID.ToString();

                    sqlStatement = String.Empty;

                    if (copyResults)
                    {
                        sqlStatement += " INSERT INTO results(planid, caseid, environmentid, result, referencenumber, notes, executiondate, userid, active)";
                        sqlStatement += " ( SELECT :NEWPLANID as planid, :NEWCASEID as caseid, environmentid, result, referencenumber, notes, executiondate, userid, active FROM results WHERE results.caseid = :OLDCASEID );";
                    }

                    if (copyTesters)
                    {
                        sqlStatement += " INSERT INTO casetesters (caseid, testerid)";
                        sqlStatement += " ( SELECT :NEWCASEID as caseid, casetesters.testerid FROM casetesters WHERE casetesters.caseid = :OLDCASEID );";
                    }

                    if (copyEnvirons)
                    {
                        sqlStatement += " INSERT INTO caseenvirons (caseid, environmentid)";
                        sqlStatement += " ( SELECT :NEWCASEID as caseid, caseenvirons.environmentid FROM caseenvirons WHERE caseenvirons.caseid = :OLDCASEID );";
                    }

                    InsertDataByParams(sqlStatement, namedParams);
                }
            }
            return PutDataByParams(sqlStatement, namedParams);
        }

		public bool MovePlanByID(string id, string targetRelease)
		{
			NamedParameter relParam = new NamedParameter("RELEASEID", targetRelease);
			NamedParameter planParam = new NamedParameter("PLANID", id);
			NamedParameter[] namedParams = new NamedParameter[2] { relParam, planParam };

			string sqlStatement = "UPDATE plans SET releaseid = :RELEASEID WHERE id = :PLANID;";

			return PutDataByParams(sqlStatement, namedParams);
		}

		public bool CopyPlanByID(string id, string targetRelease, bool copyResults, bool copyTesters, bool copyEnvirons)
		{
			NamedParameter relParam = new NamedParameter("RELEASEID", targetRelease);
			NamedParameter planParam = new NamedParameter("PLANID", id);
			NamedParameter[] namedParams = new NamedParameter[2] { relParam, planParam };

            string sqlStatement = "INSERT INTO plans (creatorid, releaseid, referencenumber, description)(SELECT creatorid, :RELEASEID as releaseid, referencenumber, description FROM plans WHERE id = :PLANID) RETURNING id;";

            long newPlanID = InsertDataByParams(sqlStatement, namedParams);

            NamedParameter newPlanParam = new NamedParameter("NEWPLANID", newPlanID.ToString());
            NamedParameter newCaseParam = new NamedParameter("NEWCASEID", String.Empty);
			NamedParameter oldCaseParam = new NamedParameter("OLDCASEID", String.Empty);
			namedParams = new NamedParameter[5] { newCaseParam, oldCaseParam, relParam, planParam, newPlanParam };

			sqlStatement = "SELECT id AS OldCaseID FROM casesuites WHERE parentplanid = :PLANID";
            DataTable caseIdsTable = GetDataByParams(sqlStatement, namedParams);

            //copy these for each id 
			if (caseIdsTable != null)
			{
				for (int i = 0; i < caseIdsTable.Rows.Count; i++)
				{
					//insert new case
					oldCaseParam.Value = caseIdsTable.Rows[i][0].ToString();
					sqlStatement = "INSERT INTO caseSuites (parentplanid, nodetype, path, description, creatorid, lastmodifiedbyid, datecreated, datemodified, category, priority, steps)";
					sqlStatement += " ( SELECT :NEWPLANID as parentplanid, nodetype, path, description, creatorid, lastmodifiedbyid, ";
					sqlStatement += " datecreated, datemodified, category, priority, steps FROM casesuites WHERE id = :OLDCASEID ) RETURNING id;";
					long newCaseID = InsertDataByParams(sqlStatement, namedParams);

					newCaseParam.Value = newCaseID.ToString();

					sqlStatement = String.Empty;

					if (copyResults)
					{
						sqlStatement += " INSERT INTO results(planid, caseid, environmentid, result, referencenumber, notes, executiondate, userid, active)";
						sqlStatement += " ( SELECT :NEWPLANID as planid, :NEWCASEID as caseid, environmentid, result, referencenumber, notes, executiondate, userid, active FROM results WHERE results.caseid = :OLDCASEID );";
					}

					if (copyTesters)
					{
						sqlStatement += " INSERT INTO casetesters (caseid, testerid)";
						sqlStatement += " ( SELECT :NEWCASEID as caseid, casetesters.testerid FROM casetesters WHERE casetesters.caseid = :OLDCASEID );";
					}

					if (copyEnvirons)
					{
						sqlStatement += " INSERT INTO caseenvirons (caseid, environmentid)";
						sqlStatement += " ( SELECT :NEWCASEID as caseid, caseenvirons.environmentid FROM caseenvirons WHERE caseenvirons.caseid = :OLDCASEID );";
					}

					InsertDataByParams(sqlStatement, namedParams);
				}
			}
			return PutDataByParams(sqlStatement, namedParams);
		}

        public DataTable GetCaseSuitesByPlanID(string planID)
        {
            string sqlStatement = "SELECT parentplanid, nodetype, path, description, creatorid, lastmodifiedbyid, ";
            sqlStatement += " datecreated, datemodified, category, priority, steps";
            sqlStatement += " FROM casesuites";
            sqlStatement += " WHERE parentplanid = :PLANID";

            NamedParameter caseParam = new NamedParameter("PLANID", planID);
            NamedParameter[] namedParams = new NamedParameter[1] { caseParam };

            return GetDataByParams(sqlStatement, namedParams);
        }


		public DataTable GetPlanByID(string planID)
		{
			string sqlStatement = "SELECT ID, ReferenceNumber, Description FROM Plans WHERE Plans.ID = '" + planID + "'";

			return GetData(sqlStatement);
		}

		public bool UpdatePlanByID(DataTable planTable, string planID)
		{
			string sqlStatement = "UPDATE Plans SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, planTable);

			sqlStatement += " WHERE ID = " + SingleQuote(planID);

			return PutDataByParams(sqlStatement, namedParams);
		}

		public long InsertPlan(DataTable planTable)
		{
			NamedParameter[] namedParams = new NamedParameter[4];
			namedParams[0] = new NamedParameter("CREATORID", planTable.Rows[0]["CreatorID"].ToString());
			namedParams[1] = new NamedParameter("RELEASEID", planTable.Rows[0]["ReleaseID"].ToString());
			namedParams[2] = new NamedParameter("REFERENCENUM", planTable.Rows[0]["ReferenceNumber"].ToString());
			namedParams[3] = new NamedParameter("DESCRIPTION", planTable.Rows[0]["Description"].ToString());

			string sqlStatement = "INSERT INTO Plans (CreatorID, ReleaseID, ReferenceNumber, Description) ";
			sqlStatement += "VALUES ( :CREATORID, :RELEASEID, :REFERENCENUM, :DESCRIPTION ) RETURNING id; ";

			return InsertDataByParams(sqlStatement, namedParams);
		}

		public long InsertCase(DataTable caseTable)
		{
			NamedParameter[] namedParams = new NamedParameter[8];
			namedParams[0] = new NamedParameter("PARENTPLAN", caseTable.Rows[0]["ParentPlanID"].ToString());
			namedParams[1] = new NamedParameter("NODETYPE", caseTable.Rows[0]["NodeType"].ToString());
			namedParams[2] = new NamedParameter("PATH", caseTable.Rows[0]["Path"].ToString());
			namedParams[3] = new NamedParameter("DESCRIPTION", caseTable.Rows[0]["Description"].ToString());
			namedParams[4] = new NamedParameter("CREATORID", caseTable.Rows[0]["CreatorID"].ToString());
			namedParams[5] = new NamedParameter("CATEGORY", caseTable.Rows[0]["Category"].ToString());
			namedParams[6] = new NamedParameter("PRIORITY", caseTable.Rows[0]["Priority"].ToString());
			namedParams[7] = new NamedParameter("STEPS", caseTable.Rows[0]["Steps"].ToString());

			string sqlStatement = "INSERT INTO CaseSuites (ParentPlanID, NodeType, Path, Description, CreatorID, Category, Priority, Steps) ";
			sqlStatement += "VALUES ( :PARENTPLAN, :NODETYPE, :PATH, :DESCRIPTION, :CREATORID, :CATEGORY, :PRIORITY, :STEPS ) RETURNING id; ";

			return InsertDataByParams(sqlStatement,	namedParams);
		}

		public long InsertSuite(DataTable suiteTable)
		{
			NamedParameter[] namedParams = new NamedParameter[5];
			namedParams[0] = new NamedParameter("PARENTPLAN", suiteTable.Rows[0]["ParentPlanID"].ToString());
			namedParams[1] = new NamedParameter("NODETYPE", suiteTable.Rows[0]["NodeType"].ToString());
			namedParams[2] = new NamedParameter("PATH", suiteTable.Rows[0]["Path"].ToString());
			namedParams[3] = new NamedParameter("DESCRIPTION", suiteTable.Rows[0]["Description"].ToString());
			namedParams[4] = new NamedParameter("CREATORID", suiteTable.Rows[0]["CreatorID"].ToString());

			string sqlStatement = "INSERT INTO CaseSuites (ParentPlanID, NodeType, Path, Description, CreatorID) ";
			sqlStatement += "VALUES ( :PARENTPLAN, :NODETYPE, :PATH, :DESCRIPTION, :CREATORID ) RETURNING id; ";

            return InsertDataByParams(sqlStatement, namedParams);
		}

		public DataTable GetCaseSuitePaths(string planID, string parentPath, string startPath, string endPath, string notPaths)
		{
			string sqlStatement = "SELECT CaseSuites.ID, CaseSuites.Path FROM  CaseSuites";
			sqlStatement += " WHERE  CaseSuites.ParentPlanID = " + SingleQuote(planID);
			sqlStatement += " AND CaseSuites.Path LIKE " + SingleQuote(parentPath + "%");

            if (!String.IsNullOrEmpty(startPath))
            {
                sqlStatement += " AND CaseSuites.Path >= " + SingleQuote(startPath);
            }

            if (!String.IsNullOrEmpty(endPath))
            {
                sqlStatement += " AND CaseSuites.Path < " + SingleQuote(endPath);
            }

            if (!String.IsNullOrEmpty(notPaths))
            {
                sqlStatement += " AND CaseSuites.Path NOT LIKE " + SingleQuote(notPaths + "%");
            }

			sqlStatement += " ORDER BY  CaseSuites.Path ASC";

			return GetData(sqlStatement);
		}

		public bool UpdatePathsByID(DataTable table)
		{
			bool result = false;

			if (table != null)
			{
				for (int i = 0; i < table.Rows.Count; i++)
				{
					string sqlStatement = "UPDATE CaseSuites SET Path = " + SingleQuote(table.Rows[i]["Path"].ToString());
					sqlStatement += " WHERE ID = " + SingleQuote(table.Rows[i]["ID"].ToString());
					
					result = PutData(sqlStatement);
				}
			}

			return result;
		}

		public string GetCaseSuitePath(string planID, string parentPath)
		{
			string sqlStatement = "SELECT MAX(Path) FROM CaseSuites WHERE ParentPlanID = " + SingleQuote(planID) + " AND Path LIKE " + SingleQuote(parentPath + "%") + " AND LENGTH(Path) BETWEEN " + (parentPath.Length + 1).ToString() + " AND " + (parentPath.Length + 4).ToString();
			object obj = GetSingleValue(sqlStatement);
			if (obj == DBNull.Value)
			{
				//there are no siblings so give it parentpath + 001
				if (parentPath.Length == 0) { return "001"; }
				else { return parentPath + ".001"; }
			}
			else
			{
				string path = (string)obj;
				string incrementedPath = path.Substring(path.Length - 3, 3);
				short pathNumber = 1;
				if (Int16.TryParse(incrementedPath, out pathNumber))
				{
					pathNumber++;
					incrementedPath = pathNumber.ToString("000");
				}

				if (parentPath.Length == 0) { return incrementedPath; }
				else { return parentPath + "." + incrementedPath; }
			}
		}

		public string GetCaseSuiteCurrentPath(string parentID)
		{
			string sqlStatement = "SELECT Path FROM CaseSuites WHERE ID = " + SingleQuote(parentID);
			object obj = GetSingleValue(sqlStatement);
			if (obj == DBNull.Value)
			{
				//there are no siblings so give it parentpath + 001
				return String.Empty;
			}
			else
			{
				return (string)obj;
			}
		}

		public DataTable GetCasesByParentPath(string parentPath, string parentPlanID)
		{
			string sqlStatement = "SELECT ID, Description, Steps FROM CaseSuites WHERE CaseSuites.NodeType = 2 AND CaseSuites.Path like " + SingleQuote(parentPath + "%") + " AND CaseSuites.ParentPlanID = " + SingleQuote(parentPlanID);

			return GetData(sqlStatement);
		}

		public DataTable GetSuiteByID(string suiteID)
		{
			string sqlStatement = "SELECT Description, Steps FROM CaseSuites WHERE CaseSuites.NodeType = 1 AND CaseSuites.ID = '" + suiteID + "'";

			return GetData(sqlStatement);
		}

		public bool UpdateSuiteByID(DataTable planTable, string suiteID)
		{
			string sqlStatement = "UPDATE CaseSuites SET ";

			NamedParameter[] namedParams = CreateUpdateSqlParams(ref sqlStatement, planTable);

			sqlStatement += " WHERE ID = " + SingleQuote(suiteID);

			return PutDataByParams(sqlStatement, namedParams);
		}


		public bool DeletePlanByID(string planID)
		{
			//string sqlStatement = "DELETE FROM Plans WHERE ID = " + SingleQuote(planID);
			//return PutData(sqlStatement);
            
            NamedParameter planParam = new NamedParameter();
            planParam.Name = "PLANID";
            planParam.Value = planID;
            
            NamedParameter[] namedParams = new NamedParameter[1]{planParam};

            string sqlStatement = " DELETE FROM attachments USING casesuites, plans";
            sqlStatement += " WHERE attachments.caseid = casesuites.id AND casesuites.parentplanid = plans.id AND plans.id = :PLANID;";

            sqlStatement += " DELETE FROM casetesters USING casesuites, plans";
            sqlStatement += " WHERE casetesters.caseid = casesuites.id AND casesuites.parentplanid = plans.id AND plans.id = :PLANID;";

            sqlStatement += " DELETE FROM caseenvirons USING casesuites, plans";
            sqlStatement += " WHERE caseenvirons.caseid = casesuites.id AND casesuites.parentplanid = plans.id AND plans.id = :PLANID;";

            sqlStatement += " DELETE FROM casesuites USING plans";
            sqlStatement += " WHERE casesuites.parentplanid = plans.id AND plans.id = :PLANID;";

            sqlStatement += " DELETE FROM plans WHERE plans.id = :PLANID;";
            
            sqlStatement += " DELETE FROM results WHERE results.planid = :PLANID;";
            
            return PutDataByParams(sqlStatement, namedParams);
		}

		public bool DeleteCaseSuiteByID(string caseSuiteID)
		{
			NamedParameter caseIdParam = new NamedParameter("CASESUITEID", caseSuiteID);
			NamedParameter[] namedParams = new NamedParameter[1] { caseIdParam };

			string sqlStatement = "SELECT id, path, parentplanid FROM CaseSuites WHERE ID = :CASESUITEID";
			DataTable table = GetDataByParams(sqlStatement, namedParams);

			string path = table.Rows[0]["path"].ToString();
			string planid = table.Rows[0]["parentplanid"].ToString();
			string siblingPath = String.Empty;

			NamedParameter pathParam = new NamedParameter("CASESUITEPATH", path);
			NamedParameter pathLengthParam = new NamedParameter("PATHLENGTH", path.Length.ToString());
			NamedParameter planParam = new NamedParameter("PLANID", planid);
			namedParams = new NamedParameter[3] { pathParam, pathLengthParam, planParam };

			sqlStatement = "SELECT path FROM casesuites WHERE path > :CASESUITEPATH AND length(path) = :PATHLENGTH AND parentplanid = :PLANID ORDER BY path ASC LIMIT 1 ";

			object obj = GetSingleValueByParams(sqlStatement, namedParams);

			if(obj != DBNull.Value)
			{
				siblingPath = (string)obj;
			}

			return DeleteCaseSuiteByPath(path, siblingPath, planid);
		}

		public bool DeleteCaseSuiteByPath(string caseSuitePath, string firstSiblingPath, string planID)
		{
			NamedParameter pathParam = new NamedParameter("CASESUITEPATH", caseSuitePath + "%");
			NamedParameter planParam = new NamedParameter("PARENTPLANID", planID);

			NamedParameter[] namedParams = new NamedParameter[2] { pathParam, planParam };

			string sqlStatement = "DELETE FROM casesuites WHERE casesuites.path like :CASESUITEPATH AND casesuites.parentplanid = :PARENTPLANID;";
			
			sqlStatement += " DELETE FROM results USING casesuites";
			sqlStatement += " WHERE results.caseid = casesuites.id AND casesuites.path like :CASESUITEPATH AND casesuites.parentplanid = :PARENTPLANID;";

			sqlStatement += " DELETE FROM attachments USING casesuites";
			sqlStatement += " WHERE attachments.caseid = casesuites.id AND casesuites.path like :CASESUITEPATH AND casesuites.parentplanid = :PARENTPLANID;";

			sqlStatement += " DELETE FROM casetesters USING casesuites";
			sqlStatement += " WHERE casetesters.caseid = casesuites.id AND casesuites.path like :CASESUITEPATH AND casesuites.parentplanid = :PARENTPLANID;";

			sqlStatement += " DELETE FROM caseenvirons USING casesuites";
			sqlStatement += " WHERE caseenvirons.caseid = casesuites.id AND casesuites.path like :CASESUITEPATH AND casesuites.parentplanid = :PARENTPLANID;";

			if(!PutDataByParams(sqlStatement, namedParams)) { return false; }	//the delete statement

			NamedParameter siblingPathParam = new NamedParameter("FIRSTSIBLINGPATH", firstSiblingPath);

			namedParams = new NamedParameter[2] { siblingPathParam, planParam };

			sqlStatement = "SELECT id, path FROM casesuites";
			sqlStatement += " WHERE casesuites.path >= :FIRSTSIBLINGPATH AND casesuites.parentplanid = :PARENTPLANID";
			sqlStatement += " ORDER BY path ASC";

			DataSet ds = GetDataSetByParams(sqlStatement, namedParams); //the select statement

			if (ds != null && ds.Tables.Count > 0)
			{
				DataTable table = ds.Tables[0];
				NodePath path = new NodePath(caseSuitePath);
				PathUtil.DecrementPaths(ref table, path.Level);

				UpdatePathsByID(table);	
			}

			return true;
		}

		private enum QueryType
		{
			Select,
			Update,
			Insert,
			Delete
		}

		private NamedParameter[] CreateUpdateSqlParams(ref string sqlStatement, DataTable table)
		{
			//Function used for insert statements for SET ColName = Value part of statement.
			NamedParameter[] namedParams = null;
			//loop on the columns and create a parameter for each one
			if (table != null && sqlStatement != null)
			{
				namedParams = new NamedParameter[table.Columns.Count];

				for (int i = 0; i < table.Columns.Count; i++)
				{
					namedParams[i] = new NamedParameter();
					namedParams[i].Name = ":" + table.Columns[i].ColumnName.ToUpper();

					if (table.Columns[i].DataType == typeof(DateTime))
					{
						DateTime date = (DateTime)table.Rows[0][i];
						namedParams[i].Value = date.ToString("yyyy-MM-dd");
					}
					else
					{
						namedParams[i].Value = table.Rows[0][i].ToString();
					}
					
					sqlStatement += table.Columns[i].ColumnName + " = " + namedParams[i].Name;

					if (i != table.Columns.Count - 1)
					{
						sqlStatement += ", ";
					}
				}

				return namedParams;
			}

			return null;
		}

		public object GetSingleValueByParams(string sqlStatement, NamedParameter[] namedParams)
		{
			NpgsqlCommand cmd = null;
			object returnVal = null;

			if (conn != null & this.State == ConnectionState.Connected)
			{
				try
				{
					cmd = new NpgsqlCommand(sqlStatement, conn);

					for (int i = 0; i < namedParams.Length; i++)
					{
						//cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
						cmd.Parameters.Add(new NpgsqlParameter(namedParams[i].Name, namedParams[i].Value));
						//cmd.Parameters[i].Value = selValues[i].Value;
					}

					returnVal = cmd.ExecuteScalar();
				}
				catch (Exception ex)
				{
					return null;
				}
			}

			return returnVal;
		}

		public object GetSingleValue(string sqlStatement)
		{
			NpgsqlCommand cmd = null;
			object returnVal = null;

			if (conn != null & this.State == ConnectionState.Connected)
			{
				try
				{
					cmd = conn.CreateCommand();
					cmd.CommandText = sqlStatement;
					returnVal = cmd.ExecuteScalar();
				}
				catch
				{
					//error
					return null;
				}
			}

			return returnVal;
		}

		private bool PutData(string sqlStatement)
		{
			NpgsqlCommand cmd = null;

			if (conn != null & this.State == ConnectionState.Connected)
			{
				try
				{
					cmd = conn.CreateCommand();
					cmd.CommandText = sqlStatement;
					cmd.ExecuteNonQuery();
				}
				catch
				{
					//error
					return false;
				}
			}

			return true;
		}

		private long InsertData(string sqlStatement)
		{
            NpgsqlCommand cmd = null;
            object ido = null;
            long id = -1;

			if (conn != null & this.State == ConnectionState.Connected)
			{
				try
				{
					cmd = conn.CreateCommand();
					cmd.CommandText = sqlStatement;
					ido = cmd.ExecuteScalar();

                    if (ido != null)
                    {
                        id = Int64.Parse(ido.ToString());
                    }
				}
				catch(Exception ex)
				{
					//error
					return -1;
				}
			}

			return id;
		}

        public long InsertDataByParams(string sqlStatement, NamedParameter[] namedParams)
        {
            NpgsqlCommand cmd = null;
            object ido = null;
            long id = -1;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = new NpgsqlCommand(sqlStatement, conn);

                    for (int i = 0; i < namedParams.Length; i++)
                    {
                        //cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
                        cmd.Parameters.Add(new NpgsqlParameter(namedParams[i].Name, namedParams[i].Value));
                        //cmd.Parameters[i].Value = selValues[i].Value;
                    }

                    ido = cmd.ExecuteScalar();

                    if (ido != null)
                    {
                        id = Int64.Parse(ido.ToString());
                    }
                }
                catch (Exception ex)
                {
                    //error
                    return -1;
                }
            }

            return id;
        }

		private DataTable GetDataByParams(string sqlStatement, NamedParameter[] namedParams)
		{
			DataSet ds = GetDataSetByParams(sqlStatement, namedParams);

			if (ds != null && ds.Tables.Count > 0)
			{
				return ds.Tables[0];
			}
			else { return null; }
		}

		private DataTable GetData(string sqlStatement)
		{
			DataTable dt = new DataTable();
            NpgsqlCommand cmd = null;
            NpgsqlDataReader reader = null;

			if (conn != null & this.State == ConnectionState.Connected)
			{
				try
				{
					cmd = conn.CreateCommand();
					cmd.CommandText = sqlStatement;
					reader = cmd.ExecuteReader();

					dt.Load(reader);

					reader.Close();
				}
				catch(Exception ex)
				{
					if (reader != null && reader.IsClosed == false) { reader.Close(); }
				}
			}

			return dt;
		}

        private bool PutDataByParams(string sqlStatement, NamedParameter[] namedParams)
        {
            NpgsqlCommand cmd = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = new NpgsqlCommand(sqlStatement, conn);

                    for (int i = 0; i < namedParams.Length; i++)
                    {
                        //cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
                        cmd.Parameters.Add(new NpgsqlParameter(namedParams[i].Name, namedParams[i].Value));
                        //cmd.Parameters[i].Value = selValues[i].Value;
                    }

                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    //error
                    return false;
                }
            }

            return true;
        }

        private DataSet GetDataSetByParams(string sqlStatement, NamedParameter[] namedParams)
        {
            DataSet ds = new DataSet();

            NpgsqlCommand cmd = null;
            NpgsqlDataAdapter adapter = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = new NpgsqlCommand(sqlStatement, conn);

                    for (int i = 0; i < namedParams.Length; i++)
                    {
                        //cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
                        cmd.Parameters.Add(new NpgsqlParameter(namedParams[i].Name, namedParams[i].Value));
                        //cmd.Parameters[i].Value = selValues[i].Value;
                    }

                    adapter = new NpgsqlDataAdapter(cmd);

                    adapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    if (adapter != null) { adapter.Dispose(); }
                }
            }

            return ds;

        }
	} //end of class


}
